<?php


/** ensure this file is being included by a parent file */
defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );

/**
 * @package	
 */
class HelpDeskModelTickets extends HelpDeskModel 
{
	/**
	 * constructor
	 * @return array Array of objects containing the data from the category
	 */	
	function __construct() {
		
	}

/**
	 * Returns the query
	 * @return string The query to be used to retrieve the rows from the database
	 */
	function _buildQuery() 
	{
		$database 	= &HelpDeskConfig::getDBO();
		$user 		= &HelpDeskConfig::getUser();
		$config 	= &HelpDeskConfig::getInstance();

		// Filter these so that the displayed tickets categoryid's are IN('') the user's managed categories
			$cat_query = "";
			$cats_array = "";
			if ($cats = HelpDeskHelperManager::getCategories( $user->id )) {
				$cats_array = array();
			}
			for ($i=0; $i<count($cats); $i++) {
				$cat = $cats[$i];
				$cats_array[] = $cat->id;
			}
			if (is_array($cats_array)) {
				$cat_query = " AND t.categoryid IN ( '0', '".implode( "', '", $cats_array )."')";
			}
		
		// Use categoryid
			$categoryid = $this->getField( $config->getFieldname( 'categoryid' ) );
			$filtercat_query = "";
			if ($categoryid) {
			// get categoryid children
				$children = HelpDeskHelperCategory::getChildren( $categoryid );
			// if IN categoryid OR in children
				$filtercat_query = "";
				$filtercats_array = array( $categoryid );
				for ($i=0; $i<count($children); $i++) {
					$filtercat = $children[$i];
					$filtercats_array[] = $filtercat->id;
				}
				if (is_array($filtercats_array)) {
					$filtercat_query = " AND t.categoryid IN ('".implode( "', '", $filtercats_array )."')";
				}
			}

		$where = array();
		$search	= $this->getField( $config->getFieldname( 'search' ) );
		if (isset($search)) 
		{
			$where[] = " LOWER(t.id) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
			$where[] = " LOWER(t.subject) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
			$where[] = " LOWER(t.description) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
			$where[] = " LOWER(t.sender_userid) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
			$where[] = " LOWER(t.sender_email) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
			$where[] = " LOWER(u.username) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
			$where[] = " LOWER(u.name) LIKE '%" . $database->getEscaped( trim( strtolower( $search ) ) ) . "%'";
		}
		$where_query = count( $where ) ? "\n HAVING " . implode( ' OR ', $where ) : "";
						
		$order_query = "";
		$order = $this->getState( 'order' );
		$order_dir = $this->getState( 'order_dir' );
		switch ($order) {
			case "id":
				$order_query = " ORDER BY t.id ".strtoupper( $order_dir )." ";
			  break;
			case "categoryid":
				$order_query = " ORDER BY t.categoryid ".strtoupper( $order_dir )." ";
			  break;
			case "subject":
				$order_query = " ORDER BY t.subject ".strtoupper( $order_dir )." ";
			  break;
			case "from":
				$order_query = " ORDER BY t.sender_userid ".strtoupper( $order_dir )." ";
			  break;
			case "followup":
				$order_query = " ORDER BY t.followup ".strtoupper( $order_dir )." ";
			  break;
			case "lastmodified":
				$order_query = " ORDER BY t.last_modified_datetime ".strtoupper( $order_dir )." ";
			  break;
			case "created":
				$order_query = " ORDER BY t.created_datetime ".strtoupper( $order_dir )." ";
			  break;
			case "status":
				$order_query = " ORDER BY t.status ".strtoupper( $order_dir )." ";
			  break; 
			case "feedback":
				$order_query = " ORDER BY t.feedback_rating ".strtoupper( $order_dir )." ";
			  break; 
			default:
				$order_query = " ORDER BY t.id DESC ";
			  break;
		}
	
		$statusid	= $this->getField( $config->getFieldname( 'statusid' ) );
		$statusid_query = " AND ( t.status = '0' ) ";
		$this->_pagetitle = HelpDeskText::_( "Open Tickets" );
		if ($statusid) {
			$type = HelpDeskHelperStatus::getType( $statusid, 'name' );
			$this->_pagetitle = HelpDeskText::_( $type->title );
			if ($type->id == '1') {
				$statusid_query = " AND ( t.followup = '{$type->id}' ) ";
			} elseif ($type->id >= '0') {
				$statusid_query = " AND ( t.status = '{$type->id}' ) ";
			} else {
				$statusid_query = "";
			}
		}

		// build query
		$query = "
			SELECT 
				t.*, u.username, u.name 
			FROM 
				#__helpdesk_tickets as t
				LEFT JOIN #__users AS u ON t.sender_userid = u.id
			WHERE 1
				{$cat_query}
				{$filtercat_query}
				{$statusid_query}
				{$where_query} 
			{$order_query}
		";

		return $query;
	}
	
	/**
	 * Retrieves the data
	 * @return array Array of objects containing the data from the database
	 */
	function getSurrounding( $id ) 
	{
		$return = array();
		$return["prev"] = '';
		$return["next"] = '';
		
		// if the post vals are present, set _surrounding
		$prev = intval( HelpDeskRequest::getVar( "prev" ) );
		$next = intval( HelpDeskRequest::getVar( "next" ) );
		
		if ($prev || $next) {
			$return["prev"] = $prev;
			$return["next"] = $next;	
			$this->_surrounding = $return;
		}
			
		if (empty($this->_surrounding)) 
		{
			$allrecords = $this->getData();
			unset($found);
			$prev_id = '';
			$next_id = '';
		
			for ($i=0; $i < count($allrecords) && !isset($found); $i++) {
		
				if ($allrecords[$i]->id == $id) { 
					$found = $i; 
					$prev_num = $i - 1;
					$next_num = $i + 1;
					if (isset($allrecords[$prev_num]->id)) { $prev_id = $allrecords[$prev_num]->id; }
					if (isset($allrecords[$next_num]->id)) { $next_id = $allrecords[$next_num]->id; }
		
				}
			}
			
			$return["prev"] = $prev_id;
			$return["next"] = $next_id;	
			
			$this->_surrounding = $return;
		}

		return $this->_surrounding;
	}
	
	/**
	 * 
	 * @return unknown_type
	 */
	function save()
	{
		$mainframe 	= HelpDeskConfig::getApplication();
		$database 	= HelpDeskConfig::getDBO();
		$date 		= HelpDeskConfig::getDate();

		$userid 	= HelpDeskRequest::getVar( 'userid' );	
		$subject 	= HelpDeskRequest::getVar( 'subject' ); 
		$description= HelpDeskRequest::getVar( 'description' );

		$admin 		= HelpDeskConfig::getUser();
		$user 		= HelpDeskConfig::getUser($userid);
	
		$row = $this->getTable( 'ticket' );
		$row->bind( $_POST );
		
		// TODO Start using $row->check for server-side verification

		// invalid entries
		if ( !$userid || !$user->id || !$subject || !$description ) {
        	$this->setError( HelpDeskText::_( 'Invalid Entries' ) );
        	return false;
		}	
				
		// init new ticket	
		$row->status					= '0'; 
		$row->last_modified_by			= $admin->id;
		$row->last_modified_datetime	= $date->toMysql(); // JHTML::_('date', $date->toMysql(), "%Y-%m-%d %H:%M:%S"); // date( "Y-m-d H:i:s" );
		$row->created_datetime			= $date->toMysql(); // JHTML::_('date', $date->toMysql(), "%Y-%m-%d %H:%M:%S"); // date( "Y-m-d H:i:s" );
		$row->created_by	 			= $admin->id;
		$row->sender_email 				= $user->email; // varchar(255)
		$row->sender_userid 			= $user->id; // int(11)
		$row->sender_flag_read			= '0'; // int(1)
		
		// execute published plugins
		$args = array();
		$args['id'] = $this->_id;
		$args['isnew'] = true;
		$dispatcher = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onBeforeStoreTicketHelpDesk', $args );
		
   		// Store the entry to the database
    	if (!$row->store()) 
    	{
        	$this->setError($database->getErrorMsg());
        	return false;
    	}

		// execute published plugins
		$dispatcher	   =HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onAfterStoreTicketHelpDesk', $args );

		// if there is an attachment
		$userfile = HelpDeskArrayHelper::getValue( $_FILES, 'userfile', '', '' );
		if ($userfile['size'] > 0) {
		
			$ticket = $this->getTable( 'ticket' );
			$ticket->load((int) $row->id );
			
			// create file object
			$file = $this->getTable( 'file' );

			// execute published plugins
			$args = array();
			$args['id'] = $this->_id;
			$dispatcher	   =HelpDeskHelperPlugin::getInstance();
			$dispatcher->trigger('onBeforeStoreAttachmentHelpDesk', $args );
		
			// do upload	
			$upload = new HelpDeskFile();
			$upload->doUpload( $ticket, $user, $file, 'userfile' );

			// execute published plugins
			$dispatcher	   =HelpDeskHelperPlugin::getInstance();
			$dispatcher->trigger('onAfterStoreAttachmentHelpDesk', $args );			
			
		}
		
		// store the additional fields, if any
			// find the post values for this category
			$name = "cat".$row->categoryid."_";
			$values = HelpDeskRequest::getVar( $name, array(0), 'post', 'array' );
			
			$ticketdata = $this->getTable( 'ticketdata' );
			$ticketdata->bind($values);
			$ticketdata->ticketid = $row->id;
							
			// execute published plugins
			$dispatcher	= HelpDeskHelperPlugin::getInstance();
			$dispatcher->trigger('onBeforeStoreTicketdataHelpDesk', $args );
	
	   		// Store the entry to the database
			$ticketdata->store();
				
			// execute published plugins
			$dispatcher	= HelpDeskHelperPlugin::getInstance();
			$dispatcher->trigger('onAfterStoreTicketdataHelpDesk', $args );
		
		return $row;
	}
		
	/**
	 * Method to delete record(s)
	 *
	 * @access    public
	 * @return    boolean    True on success
	 */
	function delete()
	{
		$cids = HelpDeskRequest::getVar('cid', array (0), 'post', 'array');
		$row = & $this->getTable( 'ticket' );

		foreach ($cids as $cid)
		{
			if (!$row->delete($cid))
			{
				$this->setError($row->getErrorMsg());
				return false;
			}
			// TODO Also delete messages for the ticket from _helpdesk_messages
		}

		return true;
	}

	/**
	 * Adds Comment
	 * @return boolean yes/no on success/fail
	 */
	function addcomment() {
		global $mainframe;

		$database = HelpDeskConfig::getDBO();
		$date = HelpDeskConfig::getDate();

		$id		 	= intval ( HelpDeskArrayHelper::getValue( $_POST, "id" ) );
		$message	= strval ( htmlspecialchars ( HelpDeskArrayHelper::getValue( $_POST, "description" ) ) );

		$admin = HelpDeskConfig::getUser();
	
		// invalid entries
		if ( !$message ) {
        	$this->setError( HelpDeskText::_( 'Invalid Entries' ) );
        	return false;
		}	
	
		$ticket = $this->getTable( 'ticket' );
		$ticket->load((int) $id );
		
		// invalid ticket
		if ( !$ticket->id ) {
        	$this->setError( HelpDeskText::_( 'Invalid Ticket' ) );
        	return false;
		}	

		$resMessage = $this->getTable( 'message' );
		$resMessage->ticketid			= $ticket->id;
		$resMessage->userid_from		= $admin->id;
		$resMessage->username_from		= $admin->email;
		$resMessage->message			= $message;	
		$resMessage->datetime			= $date->toMysql(); // JHTML::_('date', $date->toMysql(), "%Y-%m-%d %H:%M:%S"); // date( "Y-m-d H:i:s" );

		// execute published plugins
		$args = array();
		$args['id'] = $this->_id;
		$dispatcher	   = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onBeforeStoreCommentAdminHelpDesk', $args );
		
		$resMessage->store();

		// execute published plugins
		$dispatcher	   = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onAfterStoreCommentAdminHelpDesk', $args );	
			
		if ($ticket->firstresponse_by == '0') { 
			$ticket->firstresponse_by = $admin->id; 
			$ticket->firstresponse_datetime = $date->toMysql(); // JHTML::_('date', $date->toMysql(), "%Y-%m-%d %H:%M:%S"); // date( "Y-m-d H:i:s" );
		}
		$ticket->status					= 3; // requires user feedback	
		$ticket->last_modified_datetime = $date->toMysql(); // JHTML::_('date', $date->toMysql(), "%Y-%m-%d %H:%M:%S"); // date( "Y-m-d H:i:s" );

   		// Store the entry to the database
    	if (!$ticket->store()) {
        	$this->setError($database->getErrorMsg());
        	return false;
    	}

		// for passing to plugins		
		$ticket->comment = 	$message;
				
		return $ticket;
	}
	
	/**
	 * Sets followup value
	 * @return boolean yes/no on success/fail
	 */
	function followup() 
	{
		$cids = HelpDeskRequest::getVar( 'cid', array(0), 'post', 'array' );
		if ($id = intval ( HelpDeskRequest::getVar( "id", '', 'post' ) )) 
		{ 
			$cids[0] = $id; 
		}

		// grab info
		$info = $this->getTable( 'ticket' );
		$info->load( (int) $cids[0] );
	
		if (!$info->id) 
		{
			$this->setError( HelpDeskText::_( 'Update Failed' ) );
			return false;
		}
	
		// change published
		if ($info->followup == 1) { $info->followup = 0; } else { $info->followup = 1; }

		// execute published plugins
		$args = array();
		$args['id'] = $info->id;
		$args['followup'] = $info->followup;
		$dispatcher	   = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onBeforeChangeFollowupHelpDesk', $args );		

		if (!$info->store()) 
		{
			$this->setError( $info->getError() );
			return false;
		}

		// execute published plugins
		$dispatcher	   = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onAfterChangeFollowupHelpDesk', $args );		
	
	    return $info;
	}

	/**
	 * adds a file
	 * @return false on fail, object on success
	 */
	function addfile() 
	{
		$mainframe 	= HelpDeskConfig::getApplication();
		$database 	= HelpDeskConfig::getDBO();
		$date 		= HelpDeskConfig::getDate();
		$user 		= HelpDeskConfig::getUser();
		
		$id		 	= HelpDeskRequest::getVar( "id", '', 'post' );
		$this->setId($id);
		
		// if there is an attachment
		$userfile = HelpDeskArrayHelper::getValue( $_FILES, 'userfile', '', '' );
		if ($userfile['size'] > 0) {

			$ticket = $this->getTable( 'ticket' );
			$ticket->load((int) $this->_id );
					
			// create file object
			$file = $this->getTable( 'file' );

			// execute published plugins
			$args = array();
			$args['id'] = $this->_id;
			$dispatcher	   = HelpDeskHelperPlugin::getInstance();
			$dispatcher->trigger('onBeforeStoreAttachmentHelpDesk', $args );
			
			// do upload	
			$upload = new HelpDeskFile();
			$upload->doUpload( $ticket, $user, $file, 'userfile' );

			// execute published plugins
			$dispatcher	   = HelpDeskHelperPlugin::getInstance();
			$dispatcher->trigger('onAfterStoreAttachmentHelpDesk', $args );	
			
			$ticket->status					= 0; // make pending again
			$ticket->last_modified_datetime = $date->toMysql(); // JHTML::_('date', $date->toMysql(), "%Y-%m-%d %H:%M:%S"); // date( "Y-m-d H:i:s" );
	
	   		// Store the entry to the database
	    	if (!$ticket->store()) {
	        	$this->setError($database->getErrorMsg());
	        	return false;
	    	}			
		}
		
		return $ticket;
	}		

	/**
	 * Changes the category value
	 * @return boolean yes/no on success/fail
	 */
	function moveticket() 
	{
		$success = false;
		
		$id = intval( HelpDeskRequest::getVar( 'id' ) );
		$categoryid = intval( HelpDeskRequest::getVar( 'categoryid' ) );

		$info = $this->getTable('ticket');
		$info->load( $id );

		if (!$info->id) {
			$this->setError( HelpDeskText::_( 'Invalid Ticket' ) );
			return $success;
		}

		if (!$categoryid) {
			$this->setError( HelpDeskText::_( 'Invalid Category' ) );
			return $success;
		}
		
		$info->categoryid = $categoryid;
		
		if ( !$info->store() ) {
			return $success;
		}
		
		return $info;
	}


	/**
	 * Sets status
	 * @return boolean yes/no on success/fail
	 */
	function setTicketStatus( $ticketid, $statusid ) {

		// grab info
		$info = $this->getTable( 'ticket' );
		$info->load( (int)$ticketid );
	
		if (!$info->id) {
			$this->setError( HelpDeskText::_( 'Update Failed' ) );
			return false;
		}
	
		// change status
		$info->status = $statusid;

		// execute published plugins
		$args = array();
		$args['id'] = $info->id;
		$args['statusid'] = $info->statusid;
		$dispatcher	   = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onBeforeChangeStatusAdminHelpDesk', $args );		

		if (!$info->store()) {
			$this->setError( $info->getError() );
			return false;
		}

		// execute published plugins
		$dispatcher	   = HelpDeskHelperPlugin::getInstance();
		$dispatcher->trigger('onAfterChangeStatusAdminHelpDesk', $args );	
		
	    return $info;
	}

	/**
	 * Changes the status value
	 * @return boolean yes/no on success/fail
	 */
	function changestatus() {
		$success = false;
		
		$id = intval( HelpDeskRequest::getVar( 'id' ) );

		switch ( strtolower( HelpDeskRequest::getVar( 'task' ) ) ) {
			case "setfeedback":
				$statusid = '3';
			  break;
			case "setclosed":
				$statusid = '2';
			  break;
			case "setpending":
				$statusid = '0';
			  break;
			default:
				return $success;
			  break;
		}
		
		$info = $this->setTicketStatus( $id, $statusid );
		
		return $info;
		
	}
}